﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using LabMS.BLL;
using LabMS.Model;
using System.Data.SqlClient;

namespace ExtendBLL
{
    public class Staff : LabMS.BLL.Staff
    {
        
        //获得教师主讲课程
        public string GetDescipline(string DesciplinesCode)
        {
            string strSql = "select Course_Name from  Course  where  Course_Code= '" + DesciplinesCode + "'";
            DataSet ds = DbHelperSQL.Query(strSql.ToString());
            string Descipline = ds.Tables[0].Rows[0]["Course_Name"].ToString();

            return Descipline;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public ExtendModule.StaffAndOther GetStaffModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select Staff.*, Lab_Code,Lab_Name, a.[Name] as Sex,b.[Name] as Subject,c.[Name] as Title,d.[Name] as Education,e.[Name] as PartTime ");
            strSql.Append(" from Staff left outer join Dictionary As A on Staff_Sex=A.Code and A.DClass ='201'");
            strSql.Append(" left join Dictionary as B on Staff_Subject=B.Code and B.DClass = 'Subject'");
            strSql.Append(" left join Dictionary as C on Staff_Title=C.Code and C.DClass = 'Position'");
            strSql.Append(" left join Dictionary as D on Staff_Education=D.Code and D.DClass = 'education'");
            strSql.Append(" left join Dictionary as E on Staff_PartTime=E.Code and E.DClass = 'parttimetype'");
            strSql.Append(" left join Lab on Staff_Lab=Lab.ID ");
            strSql.Append(" where Staff.ID=@ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = ID;

            ExtendModule.StaffAndOther model = new ExtendModule.StaffAndOther();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.Staff_Code = ds.Tables[0].Rows[0]["Staff_Code"].ToString();
                model.Staff_Name = ds.Tables[0].Rows[0]["Staff_Name"].ToString();
                model.Staff_Sex = ds.Tables[0].Rows[0]["Staff_Sex"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_Birth"].ToString() != "")
                {
                    model.Staff_Birth = DateTime.Parse(ds.Tables[0].Rows[0]["Staff_Birth"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Staff_Lab"].ToString() != "")
                {
                    model.Staff_Lab = int.Parse(ds.Tables[0].Rows[0]["Staff_Lab"].ToString());
                }
                model.Staff_Education = ds.Tables[0].Rows[0]["Staff_Education"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_GraduteTime"].ToString() != "")
                {
                    model.Staff_GraduteTime = DateTime.Parse(ds.Tables[0].Rows[0]["Staff_GraduteTime"].ToString());
                }
                model.Staff_GradInstution = ds.Tables[0].Rows[0]["Staff_GradInstution"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_TakeInJob"].ToString() != "")
                {
                    model.Staff_TakeInJob = DateTime.Parse(ds.Tables[0].Rows[0]["Staff_TakeInJob"].ToString());
                }
                model.Staff_Subject = ds.Tables[0].Rows[0]["Staff_Subject"].ToString();
                model.Staff_Professional = ds.Tables[0].Rows[0]["Staff_Professional"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_LabSeniority"].ToString() != "")
                {
                    model.Staff_LabSeniority = int.Parse(ds.Tables[0].Rows[0]["Staff_LabSeniority"].ToString());
                }
                model.Staff_Title = ds.Tables[0].Rows[0]["Staff_Title"].ToString();
                model.Staff_Disciplines = ds.Tables[0].Rows[0]["Staff_Disciplines"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_PositionTime"].ToString() != "")
                {
                    model.Staff_PositionTime = DateTime.Parse(ds.Tables[0].Rows[0]["Staff_PositionTime"].ToString());
                }
                model.Staff_Employ = ds.Tables[0].Rows[0]["Staff_Employ"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_LabTime"].ToString() != "")
                {
                    model.Staff_LabTime = DateTime.Parse(ds.Tables[0].Rows[0]["Staff_LabTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Staff_PaperNum"].ToString() != "")
                {
                    model.Staff_PaperNum = int.Parse(ds.Tables[0].Rows[0]["Staff_PaperNum"].ToString());
                }
                model.Staff_PaperLevel = ds.Tables[0].Rows[0]["Staff_PaperLevel"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_PublicationsNum"].ToString() != "")
                {
                    model.Staff_PublicationsNum = int.Parse(ds.Tables[0].Rows[0]["Staff_PublicationsNum"].ToString());
                }
                model.Staff_PublicationsLevel = ds.Tables[0].Rows[0]["Staff_PublicationsLevel"].ToString();
                model.Staff_Work = ds.Tables[0].Rows[0]["Staff_Work"].ToString();
                model.Staff_Workload = ds.Tables[0].Rows[0]["Staff_Workload"].ToString();
                model.Staff_ForeignLanguage = ds.Tables[0].Rows[0]["Staff_ForeignLanguage"].ToString();
                model.Staff_ForeignLevel = ds.Tables[0].Rows[0]["Staff_ForeignLevel"].ToString();
                model.Staff_ResearchSub = ds.Tables[0].Rows[0]["Staff_ResearchSub"].ToString();
                model.Staff_SubjectLevel = ds.Tables[0].Rows[0]["Staff_SubjectLevel"].ToString();
                model.Staff_Award = ds.Tables[0].Rows[0]["Staff_Award"].ToString();
                model.Staff_Level = ds.Tables[0].Rows[0]["Staff_Level"].ToString();
                model.Staff_PartTime = ds.Tables[0].Rows[0]["Staff_PartTime"].ToString();
                model.Staff_ExpertLevel = ds.Tables[0].Rows[0]["Staff_ExpertLevel"].ToString();
                if (ds.Tables[0].Rows[0]["Staff_Photo"].ToString() != "")
                {
                    model.Staff_Photo = (byte[])ds.Tables[0].Rows[0]["Staff_Photo"];
                }
                model.Staff_Summary = ds.Tables[0].Rows[0]["Staff_Summary"].ToString();
                model.Lab_Code = ds.Tables[0].Rows[0]["Lab_Code"].ToString();
                model.Lab_Name = ds.Tables[0].Rows[0]["Lab_Name"].ToString();
                model.Sex = ds.Tables[0].Rows[0]["Sex"].ToString();
                model.Subject = ds.Tables[0].Rows[0]["Subject"].ToString();
                model.Title = ds.Tables[0].Rows[0]["Title"].ToString();
                model.Education = ds.Tables[0].Rows[0]["Education"].ToString();
                model.PartTime = ds.Tables[0].Rows[0]["PartTime"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        public List<ExtendModule.StaffAndOther> StaffLabList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select Staff.*, Lab_Code,Lab_Name, a.[Name] as Sex,b.[Name] as Subject,c.[Name] as Title,d.[Name] as Education,e.[Name] as PartTime ");
            strSql.Append(" from Staff left outer join Dictionary As A on Staff_Sex=A.Code and A.DClass ='201'");
            strSql.Append(" left join Dictionary as B on Staff_Subject=B.Code and B.DClass = 'Subject'");
            strSql.Append(" left join Dictionary as C on Staff_Title=C.Code and C.DClass = 'Position'");
            strSql.Append(" left join Dictionary as D on Staff_Education=D.Code and D.DClass = 'education'");
            strSql.Append(" left join Dictionary as E on Staff_PartTime=E.Code and E.DClass = 'parttimetype'");
            strSql.Append(" left join Lab on Staff_Lab=Lab.ID ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = new DataSet();
            ds = DbHelperSQL.Query(strSql.ToString());

            List<ExtendModule.StaffAndOther> modelList = new List<ExtendModule.StaffAndOther>();
            int rowCount = ds.Tables[0].Rows.Count;
            if(rowCount > 0)
            {
                ExtendModule.StaffAndOther model;
                for (int n = 0; n < rowCount; n++ )
                {
                    model = new ExtendModule.StaffAndOther();
                    if (ds.Tables[0].Rows[n]["ID"].ToString() != "")
                    {
                        model.ID = int.Parse(ds.Tables[0].Rows[n]["ID"].ToString());
                    }
                    model.Staff_Code = ds.Tables[0].Rows[n]["Staff_Code"].ToString();
                    model.Staff_Name = ds.Tables[0].Rows[n]["Staff_Name"].ToString();
                    model.Staff_Sex = ds.Tables[0].Rows[n]["Staff_Sex"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_Birth"].ToString() != "")
                    {
                        model.Staff_Birth = DateTime.Parse(ds.Tables[0].Rows[n]["Staff_Birth"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["Staff_Lab"].ToString() != "")
                    {
                        model.Staff_Lab = int.Parse(ds.Tables[0].Rows[n]["Staff_Lab"].ToString());
                    }
                    model.Staff_Education = ds.Tables[0].Rows[n]["Staff_Education"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_GraduteTime"].ToString() != "")
                    {
                        model.Staff_GraduteTime = DateTime.Parse(ds.Tables[0].Rows[n]["Staff_GraduteTime"].ToString());
                    }
                    model.Staff_GradInstution = ds.Tables[0].Rows[n]["Staff_GradInstution"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_TakeInJob"].ToString() != "")
                    {
                        model.Staff_TakeInJob = DateTime.Parse(ds.Tables[0].Rows[n]["Staff_TakeInJob"].ToString());
                    }
                    model.Staff_Subject = ds.Tables[0].Rows[n]["Staff_Subject"].ToString();
                    model.Staff_Professional = ds.Tables[0].Rows[n]["Staff_Professional"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_LabSeniority"].ToString() != "")
                    {
                        model.Staff_LabSeniority = int.Parse(ds.Tables[0].Rows[n]["Staff_LabSeniority"].ToString());
                    }
                    model.Staff_Title = ds.Tables[0].Rows[n]["Staff_Title"].ToString();
                    model.Staff_Disciplines = ds.Tables[0].Rows[n]["Staff_Disciplines"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_PositionTime"].ToString() != "")
                    {
                        model.Staff_PositionTime = DateTime.Parse(ds.Tables[0].Rows[n]["Staff_PositionTime"].ToString());
                    }
                    model.Staff_Employ = ds.Tables[0].Rows[n]["Staff_Employ"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_LabTime"].ToString() != "")
                    {
                        model.Staff_LabTime = DateTime.Parse(ds.Tables[0].Rows[n]["Staff_LabTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["Staff_PaperNum"].ToString() != "")
                    {
                        model.Staff_PaperNum = int.Parse(ds.Tables[0].Rows[n]["Staff_PaperNum"].ToString());
                    }
                    model.Staff_PaperLevel = ds.Tables[0].Rows[n]["Staff_PaperLevel"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_PublicationsNum"].ToString() != "")
                    {
                        model.Staff_PublicationsNum = int.Parse(ds.Tables[0].Rows[n]["Staff_PublicationsNum"].ToString());
                    }
                    model.Staff_PublicationsLevel = ds.Tables[0].Rows[n]["Staff_PublicationsLevel"].ToString();
                    model.Staff_Work = ds.Tables[0].Rows[n]["Staff_Work"].ToString();
                    model.Staff_Workload = ds.Tables[0].Rows[n]["Staff_Workload"].ToString();
                    model.Staff_ForeignLanguage = ds.Tables[0].Rows[n]["Staff_ForeignLanguage"].ToString();
                    model.Staff_ForeignLevel = ds.Tables[0].Rows[n]["Staff_ForeignLevel"].ToString();
                    model.Staff_ResearchSub = ds.Tables[0].Rows[n]["Staff_ResearchSub"].ToString();
                    model.Staff_SubjectLevel = ds.Tables[0].Rows[n]["Staff_SubjectLevel"].ToString();
                    model.Staff_Award = ds.Tables[0].Rows[n]["Staff_Award"].ToString();
                    model.Staff_Level = ds.Tables[0].Rows[n]["Staff_Level"].ToString();
                    model.Staff_PartTime = ds.Tables[0].Rows[n]["Staff_PartTime"].ToString();
                    model.Staff_ExpertLevel = ds.Tables[0].Rows[n]["Staff_ExpertLevel"].ToString();
                    if (ds.Tables[0].Rows[n]["Staff_Photo"].ToString() != "")
                    {
                        model.Staff_Photo = (byte[])ds.Tables[0].Rows[n]["Staff_Photo"];
                    }
                    model.Staff_Summary = ds.Tables[0].Rows[n]["Staff_Summary"].ToString();
                    model.Lab_Code = ds.Tables[0].Rows[n]["Lab_Code"].ToString();
                    model.Lab_Name = ds.Tables[0].Rows[n]["Lab_Name"].ToString();
                    model.Sex = ds.Tables[0].Rows[n]["Sex"].ToString();
                    model.Subject = ds.Tables[0].Rows[n]["Subject"].ToString();
                    model.Title = ds.Tables[0].Rows[n]["Title"].ToString();
                    model.Education = ds.Tables[0].Rows[n]["Education"].ToString();
                    model.PartTime = ds.Tables[0].Rows[n]["PartTime"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }
    }
}